import javax.xml.transform.Result;
import java.sql.*;
import java.util.Scanner;

public class dbstudent {
    private Connection conn=null;
    Scanner scanner =new Scanner(System.in);
    public dbstudent() {
        String driverName = "com.mysql.cj.jdbc.Driver";
        String userName ="root";
        String userPwd = "123";
        String dbName = "sm";
        String url1 = "jdbc:mysql://localhost:3306/"+dbName;
        String url2 = "?useSSL=false&serverTimezone=UTC";
        String url3 = "&userUnicode=true&characterEncoding=UTF-8";
        String url = url1+url2+url3;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url,userName,userPwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void printall() {
        String sql ="select * from student";
        try {
            PreparedStatement pdst=conn.prepareStatement(sql);
            ResultSet rs = pdst.executeQuery();
            while(rs.next()) {
                System.out.println(rs.getString("id")+" "+rs.getString("name")+" "+rs.getString("pw"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void add(){
        String sql="insert into student(id,name,pw) values(?,?,?)";
        PreparedStatement pstm;
        System.out.println("请输入id：");
        String id = scanner.next();
        System.out.println("请输入name：");
        String name = scanner.next();
        System.out.println("请输入pw：");
        String pw = scanner.next();
        try {
            pstm=conn.prepareStatement(sql);
            pstm.setString(1, id);
            pstm.setString(2, name);
            pstm.setString(3, pw);
            pstm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        printall();
    }

    public void delete(){
        String sql="delete from student where id=?";
        System.out.println("请输入id：");
        String id = scanner.next();
        try {
            PreparedStatement pdst = conn.prepareStatement(sql);
            pdst.setString(1,id);
            pdst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void update(){
        String sql="update student set pw=? where id=?";
        System.out.println("请输入id：");
        String id=scanner.next();
        System.out.println("请输入pw：");
        String pw=scanner.next();
        try {
            PreparedStatement pdst = conn.prepareStatement(sql);
            pdst.setString(1,pw);
            pdst.setString(2,id);
            pdst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}